# 获得整个rule_flatten表
import json
import pandas as pd
import os
import sys

# 添加父目录到路径，以便导入config和common模块
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from config import create_db_engine
from common.rule_flatten_handle import flatten_json, unflatten_json

# 创建数据库引擎（本地）
from sqlalchemy import create_engine, text, load_env_db
engine = create_db_engine()

# 创建数据库引擎（云端）
DB_USER_CLOUD, DB_PASSWORD_CLOUD, DB_HOST_CLOUD, DB_PORT_CLOUD, _, DB_DATABASE_CLOUD = load_env_db()
DATABASE_URL_CLOUD = f"mysql+pymysql://{DB_USER_CLOUD}:{DB_PASSWORD_CLOUD}@{DB_HOST_CLOUD}:{DB_PORT_CLOUD}/{DB_DATABASE_CLOUD}"
engine_cloud = create_engine(DATABASE_URL_CLOUD, echo=False, pool_recycle=3600)

def download_rules():
    # 删除本地数据库中的 rule_flatten 表。用过程，如果有就删除
    print("Dropping local rule_flatten table if exists...")
    with engine.connect() as conn:
        conn.execute(text("""
            BEGIN
                EXECUTE IMMEDIATE 'DROP TABLE rule_flatten';
            EXCEPTION
                WHEN OTHERS THEN
                    IF SQLCODE != -942 THEN
                        RAISE;
                    END IF;
            END;
        """))

    # 从云端数据库中读取 rule_flatten 表的数据
    print("Fetching rule_flatten table from cloud database...")
    with engine_cloud.connect() as conn_cloud:
        sql = text("SELECT * FROM rule_flatten")
        result = conn_cloud.execute(sql)
        rows = result.fetchall()
        columns = result.keys()
        df = pd.DataFrame(rows, columns=columns)
    
    # 将数据写入本地数据库中的 rule_flatten 表
    print("Inserting data into local rule_flatten table...")
    with engine.connect() as conn:
        df.to_sql('rule_flatten', con=conn, if_exists='replace', index=False)
    
    print("Rule flatten table has been downloaded and saved locally.")
    return df

if __name__ == "__main__":
    download_rules()